{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data/chengjiao-北京/2019-08-02-北京.xls\n",
      "data/chengjiao-北京/2019-08-03-北京.xls\n",
      "data/chengjiao-北京/2019-08-07-北京.xls\n",
      "data/chengjiao-北京/2019-08-09-北京.xls\n",
      "data/chengjiao-北京/2019-08-16-北京.xls\n",
      "data/chengjiao-北京/2019-08-22-北京.xls\n",
      "data/chengjiao-北京/2019-08-24-北京.xls\n",
      "data/chengjiao-北京/2019-08-30-北京.xls\n",
      "data/chengjiao-北京/2019-09-05-北京.xls\n",
      "data/chengjiao-北京/2019-09-06-北京.xls\n",
      "data/chengjiao-北京/2019-09-07-北京.xls\n",
      "data/chengjiao-北京/2019-09-09-北京.xls\n",
      "data/chengjiao-北京/2019-09-12-北京.xls\n",
      "data/chengjiao-北京/2019-09-14-北京.xls\n",
      "data/chengjiao-北京/2019-09-20-北京.xls\n",
      "data/chengjiao-北京/2019-09-27-北京.xls\n",
      "data/chengjiao-北京/all.csv\n",
      "data/chengjiao-北京/chengjiao-2019-08-01-北京.xls\n",
      "data/chengjiao-北京/北京.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/alex/Quant/code/LianjiaSpider/common.py:87: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=True'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass sort=False\n",
      "\n",
      "  df = pd.concat(dfs)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "513720\n"
     ]
    }
   ],
   "source": [
    "from common import read\n",
    "bj = read('北京')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from common import plot\n",
    "bj = bj.dropna(subset=['小区'])\n",
    "\n",
    "def plotArea(area):\n",
    "    df = bj.loc[bj['小区'].str.contains(area)]\n",
    "    gp = df.groupby(['成交时间'])['成交价(元/平)']\n",
    "    res=pd.DataFrame({\"volume\":gp.size(),\"median_price\":gp.median(), \"mean_price\":gp.mean()})\n",
    "    res = res.iloc[:len(res),:]\n",
    "    city = 'default'\n",
    "    MA = True\n",
    "    start_date = None\n",
    "    force = True\n",
    "    keep_all = True\n",
    "    for ma_length in [1, 30, 60, 90]:\n",
    "        title = '%s-%d日均线'%(area, ma_length)\n",
    "        plot(res, city, title, MA, ma_length, start_date, force, keep_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2011-07-16 00:00:00 2019-08-14 00:00:00\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/matplotlib/font_manager.py:1328: UserWarning: findfont: Font family ['sans-serif'] not found. Falling back to DejaVu Sans\n",
      "  (prop.get_family(), self.defaultFamily[fontext]))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2011-07-16 00:00:00 2019-08-14 00:00:00\n",
      "2011-07-16 00:00:00 2019-08-14 00:00:00\n",
      "2011-07-16 00:00:00 2019-08-14 00:00:00\n"
     ]
    }
   ],
   "source": [
    "plotArea('马甸南村')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2011-10-15 00:00:00 2019-09-08 00:00:00\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/matplotlib/font_manager.py:1328: UserWarning: findfont: Font family ['sans-serif'] not found. Falling back to DejaVu Sans\n",
      "  (prop.get_family(), self.defaultFamily[fontext]))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2011-10-15 00:00:00 2019-09-08 00:00:00\n",
      "2011-10-15 00:00:00 2019-09-08 00:00:00\n",
      "2011-10-15 00:00:00 2019-09-08 00:00:00\n"
     ]
    }
   ],
   "source": [
    "plotArea('六铺炕')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成交时间</th>\n",
       "      <th>户型</th>\n",
       "      <th>小区</th>\n",
       "      <th>售价(万)</th>\n",
       "      <th>建筑面积</th>\n",
       "      <th>成交价(元/平)</th>\n",
       "      <th>建成时间</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>链家编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101105056098</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>3室1厅1厨1卫</td>\n",
       "      <td>六铺炕二区</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>69.30</td>\n",
       "      <td>144301.0</td>\n",
       "      <td>1983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101104341063</th>\n",
       "      <td>2019-07-05</td>\n",
       "      <td>2室1厅1厨1卫</td>\n",
       "      <td>六铺炕二区</td>\n",
       "      <td>838.0</td>\n",
       "      <td>61.70</td>\n",
       "      <td>135819.0</td>\n",
       "      <td>1987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101104434522</th>\n",
       "      <td>2019-07-17</td>\n",
       "      <td>2室1厅1厨1卫</td>\n",
       "      <td>六铺炕</td>\n",
       "      <td>565.0</td>\n",
       "      <td>50.34</td>\n",
       "      <td>112237.0</td>\n",
       "      <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101104459677</th>\n",
       "      <td>2019-07-22</td>\n",
       "      <td>3室1厅1厨1卫</td>\n",
       "      <td>六铺炕二区</td>\n",
       "      <td>907.0</td>\n",
       "      <td>92.81</td>\n",
       "      <td>97727.0</td>\n",
       "      <td>1958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101105303277</th>\n",
       "      <td>2019-09-05</td>\n",
       "      <td>2室1厅1厨1卫</td>\n",
       "      <td>六铺炕二区</td>\n",
       "      <td>828.0</td>\n",
       "      <td>61.60</td>\n",
       "      <td>134416.0</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101104900792</th>\n",
       "      <td>2019-09-08</td>\n",
       "      <td>2室1厅1厨1卫</td>\n",
       "      <td>六铺炕三区</td>\n",
       "      <td>1090.0</td>\n",
       "      <td>77.70</td>\n",
       "      <td>140284.0</td>\n",
       "      <td>1999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    成交时间        户型     小区   售价(万)   建筑面积  成交价(元/平)  建成时间\n",
       "链家编号                                                                    \n",
       "101105056098  2019-07-02  3室1厅1厨1卫  六铺炕二区  1000.0  69.30  144301.0  1983\n",
       "101104341063  2019-07-05  2室1厅1厨1卫  六铺炕二区   838.0  61.70  135819.0  1987\n",
       "101104434522  2019-07-17  2室1厅1厨1卫    六铺炕   565.0  50.34  112237.0  1980\n",
       "101104459677  2019-07-22  3室1厅1厨1卫  六铺炕二区   907.0  92.81   97727.0  1958\n",
       "101105303277  2019-09-05  2室1厅1厨1卫  六铺炕二区   828.0  61.60  134416.0  1990\n",
       "101104900792  2019-09-08  2室1厅1厨1卫  六铺炕三区  1090.0  77.70  140284.0  1999"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = bj.loc[bj['小区'].str.contains('六铺炕')]\n",
    "df = df.loc[df['成交时间'] > '2019-07-01'].sort_values('成交时间')\n",
    "display(df.loc[:,['成交时间','户型','小区', '售价(万)', '建筑面积', '成交价(元/平)','建成时间']])\n",
    "#df.iloc[4]['网址']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012-02-12 00:00:00 2019-07-24 00:00:00\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/matplotlib/font_manager.py:1328: UserWarning: findfont: Font family ['sans-serif'] not found. Falling back to DejaVu Sans\n",
      "  (prop.get_family(), self.defaultFamily[fontext]))\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012-02-12 00:00:00 2019-07-24 00:00:00\n",
      "2012-02-12 00:00:00 2019-07-24 00:00:00\n",
      "2012-02-12 00:00:00 2019-07-24 00:00:00\n"
     ]
    }
   ],
   "source": [
    "plotArea('新风北街')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
